﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ManageTrainingRegister_SearchTraining

    Public Sub New()

    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal RegStatus As String, ByVal CheckStatus As String, ByVal GroupID As String, ByVal annual As String) As DataTable

        Dim dat As New clsData

        Dim sql As New MyStringBuilder

        sql.AppendFormat("SELECT distinct ")
        ' sql.AppendFormat("B.UOrganizationID, ")
        sql.AppendFormat("A.VolunteerId,F.VolunteerName,C.Text as GroupTypeText,B.GroupName,B.AllHour, B.StartDate ")
        sql.AppendFormat(",D.Text as RegStatusText,E.Text as CheckStatusText ")
        sql.AppendFormat("FROM TrainingRegister A ")
        sql.AppendFormat("inner join TrainingCourseGroup B on A.GroupId=B.Id ")
        sql.AppendFormat("LEFT JOIN CodeMapping C on B.GroupType=C.Value and C.TableName='TrainingCourseGroup' and C.ColumnName='GroupType' ")
        sql.AppendFormat("LEFT JOIN CodeMapping D on A.RegStatus=D.Value and D.TableName='TrainingRegister' and D.ColumnName='RegStatus' ")
        sql.AppendFormat("LEFT JOIN CodeMapping E on A.CheckStatus=E.Value and E.TableName='TrainingRegister' and E.ColumnName='CheckStatus' ")

        sql.AppendFormat("INNER JOIN Volunteer F on A.VolunteerID = F.VolunteerID ")

        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.DirectUOrganizationID,vm.VolunteerID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on F.VolunteerID=oo.VolunteerID  ")

        sql.AppendFormat("WHERE 1=1 ")
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0} ) ", VOrganizationID)
        End If
        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}'  )", UOrganizationID)
        End If
        ' sql.AppendFormat("(F.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (F.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (F.VillageID ='{0}' or '{0}' ='' ) ", Region2)

        If VolunteerId <> "" Then
            sql.AppendFormat("AND (F.VolunteerId = '{0}' ) ", VolunteerId)
        End If

        If VolunteerName <> "" Then
            sql.AppendFormat("AND (F.VolunteerName like '%{0}%' ) ", VolunteerName)
        End If

        If TEL <> "" Then
            sql.AppendFormat("AND (((F.MobilePhone like '%{0}%' ) or (F.OfficePhone like '%{0}%' ))) ", TEL)
        End If

        If RegStatus <> "" Then
            sql.AppendFormat("AND A.RegStatus in ('{0}') ", RegStatus)

        End If

        If CheckStatus <> "" Then
            sql.AppendFormat("AND A.CheckStatus in ('{0}') ", CheckStatus)

        End If
        'sql.AppendFormat("AND (B.UOrganizationID ='{0}' or '{0}' ='' ) ", UOrganizationID)

        If GroupID <> "" Then
            sql.AppendFormat("and B.ID = {0} ", GroupID)
        End If

        'sql.AppendFormat("ORDER BY A.VolunteerID ")

        If annual <> "" Then
            sql.AppendFormat("and year(StartDate) = {0} ", annual)
        End If
        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

End Class
